﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;

namespace DataLayer.Entities
{
    class dbConnect
    {
       
        private static String strConnectionString = "Provider=SQLOLEDB;Server=DEV02-PC;Database=StoreNetwork;Uid=sa; Pwd=1234567;";
            
        public dbConnect()
        {
            OleDbConnection m_OledbConnection;
            m_OledbConnection = new OleDbConnection(strConnectionString);
            m_OledbConnection.Open();
        }

        public static DataTable ExecuteQuery(string sqlString)
        {
            OleDbConnection m_OledbConnection = new OleDbConnection(strConnectionString);
        
            m_OledbConnection.Open();

            DataTable dt = new DataTable();

            OleDbDataAdapter m_OledbAdapter = new OleDbDataAdapter(sqlString, m_OledbConnection);

            m_OledbAdapter.Fill(dt);
        
            return dt;
        }
    
        public static bool ExecuteNonQuery(String sqlString)
        {
            OleDbConnection m_OledbConnection;
            OleDbCommand m_OledbCommand;
            m_OledbConnection = new OleDbConnection(strConnectionString);        
            m_OledbConnection.Open();


            m_OledbCommand = new OleDbCommand(sqlString, m_OledbConnection);
            m_OledbCommand.CommandType = CommandType.Text;
            int n = m_OledbCommand.ExecuteNonQuery();
            m_OledbConnection.Close();
            if (n > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    
        public static bool StoredProcedureExecuteNoneQuery(string procname, ArrayList Paraname, ArrayList Paravalues)
        {
            var m_OledbConnection = new OleDbConnection(strConnectionString);
            m_OledbConnection.Open();

            var m_OledbCommand = new OleDbCommand(procname, m_OledbConnection){CommandType = CommandType.StoredProcedure};


            for (int i = 0; i < Paraname.Count; i++)
            {
                m_OledbCommand.Parameters.Add(new OleDbParameter(Paraname[i].ToString(), Paravalues[i]));            
            }

            int n = m_OledbCommand.ExecuteNonQuery();

            m_OledbConnection.Close();

            if (n == 1)
            {
                return true;
            }
            else
            {
                return false;
            }        
        }
        public static OleDbDataReader StoredProcedureExecuteReader(string procname, ArrayList Paraname, ArrayList Paravalues)
        {
            var m_OledbConnection = new OleDbConnection(strConnectionString);
            m_OledbConnection.Open();

            var m_OledbCommand = new OleDbCommand(procname, m_OledbConnection) { CommandType = CommandType.StoredProcedure };

            var tb = new DataTable();

        
            for (int i = 0; i < Paraname.Count; i++)
            {
                m_OledbCommand.Parameters.Add(new OleDbParameter(Paraname[i].ToString(), Paravalues[i]));
            }

            OleDbDataReader datareader;

            return datareader = m_OledbCommand.ExecuteReader();
        
        }

     }
}
